package org.example.aitest.utils.gen;

import com.alibaba.fastjson2.util.DateUtils;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.example.aitest.dto.TableColumn;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZonedDateTime;
import java.util.*;

import static org.hibernate.query.criteria.internal.ValueHandlerFactory.isNumeric;

/**
 * @date 2025年05月05日
 */
@Component
public class ExcelComp {
    @Autowired
    public DynamicTableGenerator dynamicTableGenerator;
    @Autowired
    public DynamicDataService dataService;
    @Autowired
    private ApplicationContext applicationContext;

    /**
     * @description: TODO 创建新表，结构包含上传的excel文件，以及传入的serve接口方法调用关联的数据库所有数据-只要关联能查到
     * @date 2025/5/5
     * @version: 1.0
     * 参数1：excel文件
     * 2：所有数据库->数据库所有列属性集合
     * 3：查询关联表的数据类型
     * 4：对该表的查询service接口类型
    */
    public List<TableColumn> createTable(MultipartFile file, List<Map<String, Object>> ysbg_xlsx_0List,Class<?> clazz,Class<?> serve) throws Exception {
        String name = file.getOriginalFilename();
        // excel文件表，以及确定的表行每列数据
        List<TableMsg> compare = ExcelUtil.compare(file);

        Boolean isP = false;
        for (TableMsg tableMsg : compare) {
            String cellYName = tableMsg.getCellYName();
            String cellName = tableMsg.getCellName();
            for (Map<String, Object> stringMap : ysbg_xlsx_0List) {
                    String columnComment = stringMap.get("columnComment").toString();
                    if(columnComment.equals(cellYName)){ //表注释和excel列名完全对应
                        List<Object> list = tableMsg.getList();
                        for (Object object : list) {

//                        wrapper.eq(cellName,object);
//                        // 匹配上的数据
//                        YsbgXlsx_0 one = ysbgXlsx_0Service.getOne(wrapper);
                            Object instance = clazz.getDeclaredConstructor().newInstance();
                            Method method = clazz.getDeclaredMethod("getMapType");
                            Map<String,Class<?>> entityMap = (Map<String, Class<?>>) method.invoke(instance);
                            for (String s : entityMap.keySet()) {
                                if(s.equals(cellName)){
                                    Class<?> aClass = entityMap.get(s);
                                    Method declaredMethod = clazz.getDeclaredMethod("set" + toCamelCase(cellName,true), aClass);
                                    if(aClass.getCanonicalName().contains("BigDecimal")){
                                        declaredMethod.invoke(instance,new BigDecimal(String.valueOf(object)));
                                    }else if(aClass.getCanonicalName().contains("Date")){
                                        declaredMethod.invoke(instance, DateUtils.parseDate((String) object));
                                    }else {
                                        declaredMethod.invoke(instance, object.toString());
                                    }
                                }
                            }


                            Object bean = applicationContext.getBean(serve);
                            Method getData = serve.getMethod("getData", clazz);
                            Object invoke = getData.invoke(bean, instance);
                            if(!Objects.isNull(invoke)){ // 列名完全对应，并且数据对应，要求excel中有数据的列该列数值有唯一性
                                // 有匹配上的数据
                                isP = true;
                                break;
                            }
                        }
                    }
                }

        }

        String res_name = "";
        //表字段处理
        List<TableColumn> list1 = new ArrayList<>();
        if(isP){ // 有匹配上内容则证明该数据对应，因为匹配上都是有唯一性的数据
            //接下来做数据填充--做数据库还是直接生成excel表？--选择做数据库，直接生成excel表需要知道具体填充位置，毕竟麻烦
            //先将匹配上的原表内容加入
            for (TableMsg tableMsg : compare) {
                Integer bNo = tableMsg.getBNo();
                String tableName = PinyinUtils.getFirstLetters(name + "_bak_"+bNo);
                //对表名处理
                res_name = PinyinUtils.sanitizeTableName(tableName);
                // 将关联表的所有表字段设置进新表
                for (Map<String, Object> objectMap : ysbg_xlsx_0List) {
                    TableColumn tableColumn = new TableColumn();
                    String columnName = objectMap.get("columnName").toString();
                    if(columnName.equals("id") || columnName.equals("create_time") || columnName.equals("update_time") ){ //排除id,创建时间和更新时间
                        continue;
                    }
                    String columnType = objectMap.get("columnType").toString();
                    int[] ints = DbTypeParser.parseTypeInfo(columnType);
                    String dataType = objectMap.get("dataType").toString();
                    String columnComment = objectMap.get("columnComment").toString();

                    tableColumn.setName(columnName);
                    tableColumn.setType(dataType);
                    tableColumn.setLength(ints[0]);
                    tableColumn.setComment(columnComment);
                    list1.add(tableColumn);
                }


                // 将excel中有的列，而数据库没有的添加进新表
                XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());
                XSSFSheet sheet = excel.getSheetAt(tableMsg.getBNo());
                XSSFRow row = sheet.getRow(tableMsg.getHNo());
                int lastCellNum = row.getLastCellNum();
                //命名集合，为了对重名做处理才添加
                List<String> list = new ArrayList<>();
                //备注集合
                List<String> remarkList = new ArrayList<>();
                for (int i = 0; i < lastCellNum; i++) {
                    Boolean isCF = false;
                    //排除掉已经设置的列属性
                    XSSFCell cell = row.getCell(i);
                    DataFormatter formatter = new DataFormatter();
                    String stringCellValue = formatter.formatCellValue(cell);
                    for (Map<String, Object> map : ysbg_xlsx_0List) {
                        String columnComment = map.get("columnComment").toString();
                        if(stringCellValue.equals(columnComment)){
                            isCF = true;
                            break;
                        }
                    }
                    if(isCF){
                        continue;
                    }
                    //取首拼音
                    String firstLetters = PinyinUtils.getFirstLetters(stringCellValue);
                    //对命名做规范处理
                    String name1 = PinyinUtils.sanitizeTableName(firstLetters);
                    list.add(name1);
                    remarkList.add(stringCellValue);
                }

                //对命名集合做重名处理
                for (int i = 0; i < list.size(); i++) {
                    for (int j = i+1; j < list.size(); j++) {
                        if(list.get(i).equals(list.get(j))){
                            //依次与后面所有数据比较，若相同则重名，重名加后缀
                            String s = list.get(i);
                            list.remove(j);
                            list.add(j,s+"_"+j);
                        }
                    }
                }

                //未设置的数据都统一设置为字符串类型
                for (int i = 0; i < list.size(); i++) {
                    String s = list.get(i);
                    TableColumn tableColumn = new TableColumn();
                    tableColumn.setName(s);
                    tableColumn.setType("VARCHAR");
                    tableColumn.setLength(100);
                    tableColumn.setComment(remarkList.get(i));
                    list1.add(tableColumn);
                }
//                dynamicTableGenerator.createTable(res_name,list1, name,2);
            }
        }

        return list1;
    }

    /**
     * TODO 往新表中添加匹配数据-包括excel表已有数据和数据库匹配的表数据
     * 参数1：excel文件
     * 参数2：关联数据库的数据类型
     * 参数3：关联数据库的service接口类型
     * */
    public List<Map<String,Object>> insertData(MultipartFile file,Class<?> clazz,Class<?> serve) throws Exception {
        String name = file.getOriginalFilename();
        XSSFWorkbook excel = new XSSFWorkbook(file.getInputStream());
        //往新表中添加数据
        List<TableMsg> compare = ExcelUtil.compare(file);
        TableMsg tableMsg = compare.get(0);
        Integer bNo = tableMsg.getBNo();
        Integer hNo = tableMsg.getHNo();
        Integer lNo = tableMsg.getLNo();
        String cellName = tableMsg.getCellName();
        List<Object> msgList = tableMsg.getList();

        XSSFSheet sheet = excel.getSheetAt(bNo);
        //设置列名集合
        List<String> lNames = new ArrayList<>();
        XSSFRow row1 = sheet.getRow(hNo);
        int lastCellNum1 = row1.getLastCellNum();
        for (int i = 0; i < lastCellNum1; i++) {
            XSSFCell cell = row1.getCell(i);
            DataFormatter formatter = new DataFormatter();
            String stringCellValue = formatter.formatCellValue(cell);
            String firstLetters = PinyinUtils.getFirstLetters(stringCellValue);
            String sanitizeTableName = PinyinUtils.sanitizeTableName(firstLetters);
            lNames.add(sanitizeTableName);
        }


//        String firstLetters = PinyinUtils.getFirstLetters(name + "_bak_" + bNo);
//        String tableName = PinyinUtils.sanitizeTableName(firstLetters);
        List<Map<String,Object>> list = new ArrayList<>();
        Boolean isNum = false;
        //所有行
        int lastRowNum = sheet.getLastRowNum() + 1;
        for (int i = hNo + 1; i < lastRowNum; i++) {
            //一行数据
            Map<String,Object> map = new HashMap<>();
            XSSFRow row = sheet.getRow(i);
            //所有列
            int lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                XSSFCell cell = row.getCell(j);
                if(cell != null){
                    DataFormatter formatter = new DataFormatter();
                    String stringCellValue = formatter.formatCellValue(cell);
                    //添加excel表中数据
                    map.put(lNames.get(j),stringCellValue);
                    // 记录添加数据库数据前数据量
                    int perNum = map.size();
                    //添加数据库中数据
//                    for (Object o : msgList) {
                    extracted(lNames, map, j,cell,clazz,serve);
                    //添加数据库数据后数据量
                    int lastNum = map.size();
                    if(perNum < lastNum){
                        // 证明数据库有匹配的数据
                        isNum = true;
                    }
//                    }
                }
            }
            list.add(map);
//            dataService.insertData(tableName,map);
        }
        if(isNum){
            return list;
        }else {
            list.clear();
            return list;
        }
    }

    private void extracted(List<String> lNames, Map<String, Object> map, int j,XSSFCell cell,Class<?> clazz,Class<?> serve) throws Exception {
        //反射调用实体类的方法，获取各个参数和其对应类型
        Object instance = clazz.getDeclaredConstructor().newInstance();
        Method method = clazz.getDeclaredMethod("getMapType");
        Map<String,Class<?>> entityMap = (Map<String, Class<?>>) method.invoke(instance);
        for (String s : entityMap.keySet()) {
            if(s.equals(lNames.get(j))){
                String camelCase = toCamelCase(lNames.get(j), true);
                Class<?> aClass = entityMap.get(s);
                Method declaredMethod = clazz.getDeclaredMethod("set" + camelCase, aClass);
                switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                        declaredMethod.invoke(instance,cell.getStringCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            //时间
                            DataFormatter formatter = new DataFormatter();
                            declaredMethod.invoke(instance,formatter.formatCellValue(cell));
                        } else {
                            // 数字
                            DataFormatter formatter = new DataFormatter();
                            declaredMethod.invoke(instance,new BigDecimal(formatter.formatCellValue(cell)));
                        }
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        declaredMethod.invoke(instance,cell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        declaredMethod.invoke(instance,cell.getCellFormula());
                        break;
                    default:
                        break;
                }

                Object bean = applicationContext.getBean(serve);
                Method getData = serve.getMethod("getData", clazz);
                Object invoke = getData.invoke(bean, instance);
//            YsbgXlsx_0 ysbgXlsx_0 = ysbgXlsx_0Service.getOne(wrapper);
                if (!Objects.isNull(invoke)) {
                    //移除已经添加的字段值
                    Method getMap = clazz.getDeclaredMethod("getMap");
                    Map<String, Object> map1 = (Map<String, Object>) getMap.invoke(invoke);
//                Map<String, Object> map1 = ysbgXlsx_0.getMap();
                    System.out.println("----------" + map1);
                    if (!map1.isEmpty()) {
                        for (String s1 : map1.keySet()) {
                            //移除已经添加过的值
                            if (s1.equals(lNames.get(j))) {
//                                            map1.remove(s);
                                continue;
                            }
                            Object object = map1.get(s1);
                            if(isDateTimeType(object)){
                                map.put(s1, org.example.aitest.utils.DateUtils.parseDate2String((Date) map1.get(s1),"yyyy-MM-dd HH:MM:SS"));
                            }else {
                                map.put(s1, map1.get(s1));
                            }
                        }
                    }
                }
            }else {
                continue;
            }
        }
    }

//    public void addTableToExcel(List<Map<String, Object>> srhtXlsxBak_0ServiceCm, XSSFWorkbook excel, List<SrhtXlsxBak> srhtXlsxBak_0ServiceAll) throws Exception {
//        int lsize = srhtXlsxBak_0ServiceCm.size();
//
//
//        // 创建新文件
//        XSSFSheet sheet = excel.createSheet("匹配后数据表");
//        // 创建行,数据库数据集合长度加一列注释
//        XSSFRow row = sheet.createRow(srhtXlsxBak_0ServiceAll.size()+1);
//
//        // 第一行为注释内容
//        XSSFRow row1 = sheet.getRow(0);
//        //防止排除不需要字段后出现空列
//        Boolean isJ = false;
//        int j = 1;
//
//        for (int p = 0; p < lsize; p++) {
//            if(isJ){
//                j--;
//            }
//            Map<String, Object> objectMap = srhtXlsxBak_0ServiceCm.get(p);
//            String columnName = objectMap.get("columnName").toString();
//            if(columnName.equalsIgnoreCase("id")
//                    || columnName.equalsIgnoreCase("create_time")
//                    || columnName.equalsIgnoreCase("update_time")){ //排除不需要的字段
//                isJ = true;
//                continue;
//            }
//
//            if(row1 == null){
//                row1 = sheet.createRow(j);
//            }
//            XSSFCell cell1 = row1.getCell(j);
//            if(cell1 == null){
//                cell1 = row1.createCell(j);
//            }
//            cell1.setCellValue(objectMap.get("columnComment").toString());
//
//            isJ = false;
//            j++;
//        }
//
//        //第二行开始添加数据库内容
//        for (int p = 1; p < srhtXlsxBak_0ServiceAll.size()+1; p++) {
//            XSSFRow row2 = sheet.getRow(p);
//            if(row2 == null){
//                row2 = sheet.createRow(p);
//            }
//            SrhtXlsxBak srhtXlsxBak0 = srhtXlsxBak_0ServiceAll.get(p - 1);
//            if(Objects.nonNull(srhtXlsxBak0)) {
//                List<Object> allGet = srhtXlsxBak0.getAllGet();
//                for (int k = 0; k < allGet.size(); k++) {
//                    Object allDataType = allGet.get(k);
//                    if(Objects.nonNull(allDataType)) {
//                        XSSFCell cell1 = row2.getCell(k);
//                        if (cell1 == null) {
//                            cell1 = row2.createCell(k);
//                        }
//                        if (allDataType instanceof String) {
//                            cell1.setCellValue((String) allDataType);
//                        } else if (allDataType instanceof BigDecimal) {
//                            cell1.setCellValue(allDataType.toString());
//                        } else if (isDateTimeType(allDataType)) {
//                            cell1.setCellValue(org.example.aitest.utils.DateUtils.parseDate2String((Date) allDataType));
//                        }
//                    }else {
//                        continue;
//                    }
//                }
//            }
//        }
//    }

    // 判断数据是否是时间类型
    public boolean isDateTimeType(Object obj) {
        return obj instanceof Date ||
                obj instanceof java.sql.Date ||
                obj instanceof Timestamp ||
                obj instanceof LocalDate ||
                obj instanceof LocalDateTime ||
                obj instanceof ZonedDateTime;
    }

    private String toCamelCase(String str, boolean isBigCamel) {
        String[] parts = str.split("_");
        StringBuilder result = new StringBuilder();

        for (int i = 0; i < parts.length; i++) {
            String part = parts[i];

            // 跳过空部分（连续下划线情况）
            if (part.isEmpty()) {
                continue;
            }

            // 处理数字情况：如果下一部分是数字，则保持原样
            if (i < parts.length - 1 && isNumeric(parts[i + 1])) {
                if (i == 0 && !isBigCamel) {
                    result.append(part.toLowerCase());
                } else {
                    result.append(part.substring(0, 1).toUpperCase())
                            .append(part.substring(1).toLowerCase());
                }
                result.append("_").append(parts[i + 1]); // 添加下划线和数字部分
                i++; // 跳过已处理的数字部分
                continue;
            }

            // 常规处理
            if (i == 0 && !isBigCamel) {
                result.append(part.toLowerCase());
            } else {
                result.append(part.substring(0, 1).toUpperCase())
                        .append(part.substring(1).toLowerCase());
            }
        }

        return result.toString();
    }
}
